package tianxiao.web.test


import org.intellij.lang.annotations.Language
import org.springframework.stereotype.Controller
import org.springframework.web.bind.annotation.GetMapping
import tianxiao.web.test.model._public.Test
import yun.web.activerecord.Db
import yun.web.activerecord.Model
import yun.web.activerecord.Page
import yun.web.activerecord.Record
import yun.web.activerecord.dialect.pg.type.PG
import yun.web.enjoy.kit.Kv

import java.math.BigDecimal
import java.util.Date

@Controller
class DbController {

    @GetMapping("/dbTest/record")
    @Throws(Exception::class)
    fun dbTest(): List<Record> {

        val table = "t_test"
        // 删除所有
        Db.update("delete from $table")

        // 构建record
        val record = Record.build(
                "int", 1, "int_arr"
                , intArrayOf(1, 2, 3)
                , "float", 1.2f
                , "floatarr", arrayOf(3.45f, 4.56f)
                , "double", 2.3
                , "douiblearr", arrayOf(1.23, 2.34)
                , "text", "张笑笑说他想骑着萌雀雀遨游无尽天际"
                , "text_arr", arrayOf("4", "5", "6"), "long", 101L
                , "long_arr", arrayOf(7L, 8L, 9L)
                , "decimal", BigDecimal("10")
                , "decimalarr", arrayOf(BigDecimal("30.23"), BigDecimal("40.34"))
                , "date", Date()
                , "datearr", arrayOf(Date(), Date())
                , "timestamptz", Date()
                , "tpzarr", arrayOf(Date(), Date())
                , "json", Record.build("name", "张天笑")
                , "jsonb", Record.build("name", "张笑笑喜欢萌雀雀")
                , "tsvector", PG.tsv("'微醺':3 '我':5 '折扇':2 '踏入':4 '轻摇':1 '闺房':6")

        )

        // 保存并返回 自增id, 默认更新时间
        Db.save("t_test", record, "updatetime", "id")

        // upsert
        record.set("text", "萌雀雀答应张笑笑了")
                .upsert(table)

        // 查询所有
        val records = Db.find("select * from t_test where true")

        val count = Db.queryInt("select count(*) from t_test where true")

        // 分页查询
        val paginate = Db.paginate(1, 1, "select * from t_test where true")
        val list = paginate.list


        // 关联 + 条件查询
        @Language("sql")
        val sql = """
            select t1.* from public.t_test t1
              inner join public.t_test t2 on t1.id = t2.id
             #p(int, int_arr)
               and t1.int > ?
               and t1.int_arr = ?
             #end
        """.trimIndent()
        val kv = mapOf<String, Any>("int" to 0, "int_arr" to intArrayOf(1, 2, 3))
        val pager = Db.paginate(1, 1, sql, Kv.by("int", 0).set("int_arr", intArrayOf(1, 2, 3)))


        return pager.getList()
    }

    @GetMapping("/dbTest/model")
    @Throws(Exception::class)
    fun modelTest(): List<Model<*>> {

        val table = "t_test"
        // 删除所有
        Db.update("delete from $table")


        // 构建record
        val model = Test.build(
                "int", 1, "int_arr", intArrayOf(1, 2, 3), "float", 1.2f
                , "floatarr", arrayOf(3.45f, 4.56f), "double", 2.3, "douiblearr"
                , arrayOf(1.23, 2.34), "text", "张笑笑说他想骑着萌雀雀遨游无尽天际"
                , "text_arr", arrayOf("4", "5", "6"), "long", 101L, "long_arr"
                , arrayOf(7L, 8L, 9L), "decimal", BigDecimal("10"), "decimalarr", arrayOf(BigDecimal("30.23"), BigDecimal("40.34")), "date", Date(), "datearr", arrayOf(Date(), Date()), "timestamptz", Date(), "tpzarr", arrayOf(Date(), Date()), "json", Record.build("name", "张天笑"), "jsonb", Record.build("name", "张笑笑喜欢萌雀雀"), "tsvector", PG.tsv("'微醺':3 '我':5 '折扇':2 '踏入':4 '轻摇':1 '闺房':6")

        )

        // 保存并返回 自增id, 默认更新时间
        model.save("updatetime", "id")

        // upsert
        model.set("text", "萌雀雀答应张笑笑了")
                .upsert()

        // 查询所有
        val models = Test.dao.find("select * from t_test where true")

        // 分页查询
        val paginate = Test.dao.paginate(1, 1, "select * from t_test where true")
        val list = paginate.list

        // 关联 + 条件查询
        @Language("sql")
        val sql = "select t1.* from public.t_test t1" +
                "  inner join public.t_test t2 on t1.id = t2.id\n" +
                "  #p(int, int_arr)\n" +
                "  and t1.int > ?\n" +
                "  and t1.int_arr = ?\n" +
                "  #end"
        val pager = Test.dao.paginate(1, 1, sql, Kv.by("int", 0).set("int_arr", intArrayOf(1, 2, 3)))


        return pager.list


    }

}
